package com.zg.common.web.service.impl;

import com.zg.common.core.R;
import com.zg.common.core.dao.ColumnConstant;
import com.zg.common.core.enm.ColumnTypeEnum;
import com.zg.common.web.service.DdlService;
import com.zg.common.core.vo.ColumnVo;
import com.zg.common.core.vo.PublicColumnExistVo;
import com.zg.common.core.vo.TreeColumnExistVo;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.*;

/**
 * @author Zxb
 */
@Service
@AllArgsConstructor
@Slf4j
public class MysqlDdlServiceImpl implements DdlService {

    private JdbcTemplate jdbcTemplate;

    @Override
    public Boolean isTableExist(String schema, String tableName) {
        String sql = " SELECT count(*) FROM information_schema.tables WHERE table_schema = ? and table_name = ? ";
        Object[] args = new Object[]{schema, tableName};
        int count = jdbcTemplate.queryForObject(sql, Integer.class, args);
        return count > 0;
    }

    @Override
    public List<String> findTable(String schema) {
        String sql = " SELECT table_name FROM information_schema.tables WHERE table_schema = ? ";
        Object[] args = new Object[]{schema};
        return jdbcTemplate.queryForList(sql, String.class, args);
    }

    @Override
    public List<String> findTableWithPrefix(String schema, String prefix) {
        String sql = " SELECT table_name FROM information_schema.tables WHERE table_schema = ? and table_name like ? ";
        Object[] args = new Object[]{schema, prefix + "%"};
        return jdbcTemplate.queryForList(sql, String.class, args);
    }

    @Override
    public List<ColumnVo> findColumnByTable(String tableName, String schema) {
        String sql = " SELECT column_name,data_type,column_comment,character_maximum_length FROM information_schema.columns WHERE table_name = ? AND table_schema = ? ";
        Object[] args = new Object[]{tableName, schema};
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            ColumnVo column = new ColumnVo();
            String name = rs.getString("column_name");
            String comment = rs.getString("column_comment");
            String type = rs.getString("data_type");
            Integer length = rs.getInt("character_maximum_length");
            column.setName(name);
            column.setComment(comment);
            column.setType(type);
            column.setLength(length);
            return column;
        }, args);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> dropColumn(String schema, String tableName, String columnName) {
        String sql1 = " use " + schema + " ; ";
        String sql2 = " alter table " + tableName + " drop column " + columnName + " ; ";
        String sql3 = " delete from gen_column where table_name='" + tableName + "' and column_name = '" + columnName + "' ; ";
        jdbcTemplate.batchUpdate(sql1, sql2, sql3);
        return R.success();
    }

    private PublicColumnExistVo validatePublicColumn(String schema, String tableName) {
        PublicColumnExistVo vo = new PublicColumnExistVo();
        List<ColumnVo> columnList = findColumnByTable(tableName, schema);
        if (columnList != null && !columnList.isEmpty()) {
            for (ColumnVo column : columnList) {
                String colName = column.getName();
                if (ColumnConstant.CREATE_TIME.equals(colName)) {
                    vo.setHasCreateTime(true);
                } else if (ColumnConstant.CREATE_BY.equals(colName)) {
                    vo.setHasCreateBy(true);
                } else if (ColumnConstant.UPDATE_TIME.equals(colName)) {
                    vo.setHasUpdateTime(true);
                } else if (ColumnConstant.UPDATE_BY.equals(colName)) {
                    vo.setHasUpdateBy(true);
                } else if (ColumnConstant.VER.equals(colName)) {
                    vo.setHasVer(true);
                } else if (ColumnConstant.IS_DEL.equals(colName)) {
                    vo.setHasIsDel(true);
                } else if (ColumnConstant.SEARCH_STR.equals(colName)) {
                    vo.setHasSearchStr(true);
                }
            }
        }
        return vo;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void addPubColumn(String schema, String tableName) {
        log.debug("开始处理表>>>>" + tableName);
        PublicColumnExistVo vo = validatePublicColumn(schema, tableName);
        List<String> sqlList = new ArrayList<>();

        if (!vo.isHasCreateTime()) {
            log.debug("开始生成字段>>>>" + tableName + ":create_time");
            String sql = " alter table " + tableName + " add column create_time datetime DEFAULT NULL COMMENT '数据创建时间' ";
            sqlList.add(sql);
            log.debug("结束生成字段>>>>" + tableName + ":create_time");
        }
        if (!vo.isHasCreateBy()) {
            log.debug("开始生成字段>>>>" + tableName + ":create_by");
            String sql = " alter table " + tableName + " add column create_by varchar(50) DEFAULT NULL COMMENT '数据创建者' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":create_by");
        }
        if (!vo.isHasUpdateTime()) {
            log.debug("开始生成字段>>>>" + tableName + ":update_time");
            String sql = " alter table " + tableName + " add column update_time datetime DEFAULT NULL COMMENT '数据更新时间' ";
            sqlList.add(sql);
            log.debug("结束生成字段>>>>" + tableName + ":update_time");
        }
        if (!vo.isHasUpdateBy()) {
            log.debug("开始生成字段>>>>" + tableName + ":update_by");
            String sql = " alter table " + tableName + " add column update_by varchar(50) DEFAULT NULL COMMENT '数据更新者' ";
            sqlList.add(sql);
            log.debug("结束生成字段>>>>" + tableName + ":update_by");
        }
        if (!vo.isHasVer()) {
            log.debug("开始生成字段>>>>" + tableName + ":ver");
            String sql = " alter table " + tableName + " add column ver int(11) DEFAULT '0' COMMENT '乐观锁' ";
            sqlList.add(sql);
            log.debug("结束生成字段>>>>" + tableName + ":ver");
        }
        if (!vo.isHasIsDel()) {
            log.debug("开始生成字段>>>>" + tableName + ":is_del");
            String sql = " alter table " + tableName + " add column is_del tinyint(1) DEFAULT '0' COMMENT '是否删除' ";
            sqlList.add(sql);
            log.debug("结束生成字段>>>>" + tableName + ":is_del");
        }
        if (!vo.isHasSearchStr()) {
            log.debug("开始生成字段>>>>" + tableName + ":search_str");
            String sql = " alter table " + tableName + " add column search_str varchar(255) DEFAULT NULL COMMENT '查询字符串' ";
            sqlList.add(sql);
            log.debug("结束生成字段>>>>" + tableName + ":search_str");
        }
        if (sqlList.size() > 0) {
            String[] sqlArr = new String[sqlList.size()];
            jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        }
        log.debug("结束处理表>>>>" + tableName);
    }

    private TreeColumnExistVo validateTreeColumn(String schema, String tableName) {
        TreeColumnExistVo vo = new TreeColumnExistVo();
        List<ColumnVo> columnList = findColumnByTable(tableName, schema);
        if (columnList != null && !columnList.isEmpty()) {
            for (ColumnVo column : columnList) {
                String colName = column.getName();
                if (ColumnConstant.NAME.equals(colName)) {
                    vo.setHasName(true);
                } else if (ColumnConstant.CODE.equals(colName)) {
                    vo.setHasCode(true);
                } else if (ColumnConstant.LAYER.equals(colName)) {
                    vo.setHasLayer(true);
                } else if (ColumnConstant.PID.equals(colName)) {
                    vo.setHasPid(true);
                } else if (ColumnConstant.NAME_LINK.equals(colName)) {
                    vo.setHasNameLink(true);
                } else if (ColumnConstant.ID_LINK.equals(colName)) {
                    vo.setHasIdLink(true);
                }
            }
        }
        return vo;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void addTreeColumn(String schema, String tableName) {
        log.info("开始处理表>>>>" + tableName);
        TreeColumnExistVo vo = validateTreeColumn(schema, tableName);
        List<String> sqlList = new ArrayList<>();

        if (!vo.isHasName()) {
            log.info("开始生成字段>>>>" + tableName + ":name");
            String sql = " alter table " + tableName + " add column name varchar(50) NULL COMMENT '名称' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":name");
        }
        if (!vo.isHasCode()) {
            log.info("开始生成字段>>>>" + tableName + ":code");
            String sql = " alter table " + tableName + " add column code bigint(20) NULL COMMENT '编码' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":code");
        }
        if (!vo.isHasLayer()) {
            log.info("开始生成字段>>>>" + tableName + ":layer");
            String sql = " alter table " + tableName + " add column layer int(11) NULL COMMENT '层级' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":layer");
        }
        if (!vo.isHasPid()) {
            log.info("开始生成字段>>>>" + tableName + ":pid");
            String sql = " alter table " + tableName + " add column pid bigint(20) NULL COMMENT '上级编号' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":pid");
        }
        if (!vo.isHasNameLink()) {
            log.info("开始生成字段>>>>" + tableName + ":nameLink");
            String sql = " alter table " + tableName + " add column name_link varchar(255) NULL COMMENT '全链路名称' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":nameLink");
        }
        if (!vo.isHasIdLink()) {
            log.info("开始生成字段>>>>" + tableName + ":idLink");
            String sql = " alter table " + tableName + " add column id_link varchar(255) NULL COMMENT '全链路编号' ";
            sqlList.add(sql);
            log.info("结束生成字段>>>>" + tableName + ":idLink");
        }
        if (sqlList.size() > 0) {
            String[] sqlArr = new String[sqlList.size()];
            jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        }
        log.info("结束处理表>>>>" + tableName);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void dropPubColumn(String schema, String tableName) {
        log.info("开始处理表>>>>" + tableName);
        PublicColumnExistVo vo = validatePublicColumn(schema, tableName);
        List<String> sqlList = new ArrayList<>();
        if (vo.isHasCreateTime()) {
            log.info("开始删除字段>>>>" + tableName + ":create_time");
            String sql = " alter table " + tableName + " drop column create_time ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":create_time");
        }
        if (vo.isHasCreateBy()) {
            log.info("开始删除字段>>>>" + tableName + ":create_by");
            String sql = " alter table " + tableName + " drop column create_by ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":create_by");
        }
        if (vo.isHasUpdateTime()) {
            log.info("开始删除字段>>>>" + tableName + ":update_time");
            String sql = " alter table " + tableName + " drop column update_time ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":update_time");
        }
        if (vo.isHasUpdateBy()) {
            log.info("开始删除字段>>>>" + tableName + ":update_by");
            String sql = " alter table " + tableName + " drop column update_by ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":update_by");
        }
        if (vo.isHasVer()) {
            log.info("开始删除字段>>>>" + tableName + ":ver");
            String sql = " alter table " + tableName + " drop column ver ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":ver");
        }
        if (vo.isHasIsDel()) {
            log.info("开始删除字段>>>>" + tableName + ":is_del");
            String sql = " alter table " + tableName + " drop column is_del ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":is_del");
        }
        if (vo.isHasSearchStr()) {
            log.info("开始删除字段>>>>" + tableName + ":search_str");
            String sql = " alter table " + tableName + " drop column search_str ";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":search_str");
        }
        if (sqlList.size() > 0) {
            String[] sqlArr = new String[sqlList.size()];
            jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        }
        log.info("结束处理表>>>>" + tableName);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> addTable(String schema, String tableName, List<ColumnVo> columnVoList) {
        StringBuilder sb = new StringBuilder();
        sb.append(" create table ").append(tableName).append(" ( ");
        sb.append(" `id` bigint(20) NOT NULL COMMENT 'id', ");
        for (ColumnVo columnVo : columnVoList) {
            String name = columnVo.getName();
            String type = columnVo.getType();
            String comment = columnVo.getComment();
            sb.append(" `" + name + "` " + ColumnTypeEnum.getOracleType(type) + " DEFAULT NULL COMMENT '" + comment + "', ");
        }
        sb.append(" `create_time` datetime DEFAULT NULL COMMENT '数据创建时间', ");
        sb.append(" `create_by` varchar(50) DEFAULT NULL COMMENT '数据创建者', ");
        sb.append(" `update_time` datetime DEFAULT NULL COMMENT '数据更新时间', ");
        sb.append(" `update_by` varchar(50) DEFAULT NULL COMMENT '数据更新者', ");
        sb.append(" `ver` int(11) DEFAULT '0' COMMENT '乐观锁', ");
        sb.append(" `is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除', ");
        sb.append(" `search_str` varchar(255) DEFAULT NULL COMMENT '查询字符串', ");
        sb.append(" PRIMARY KEY (`id`) ");
        sb.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ");
        String sql = sb.toString();
        jdbcTemplate.batchUpdate(" use " + schema + "; ", sql);
        return R.success();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> delTable(String schema, String tableName) {
        String sql1 = " use " + schema + ";";
        String sql2 = " drop table " + tableName + ";";
        String sql3 = " delete from gen_column where table_name='" + tableName + "' ; ";
        jdbcTemplate.batchUpdate(sql1, sql2, sql3);
        return R.success();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> mergeColumnsForTable(String schema, String tableName, List<ColumnVo> columnVoList) {
        List<ColumnVo> list = findColumnByTable(tableName, schema);
        List<String> sqlList = new ArrayList<>();
        for (ColumnVo columnVo : columnVoList) {
            String name = columnVo.getName();
            String type = columnVo.getType();
            String comment = columnVo.getComment();
            StringBuilder sqlSb = new StringBuilder(" ALTER TABLE `" + tableName + "` ");
            if (list.stream().anyMatch(t -> t.getName().equals(name))) {
                String str = " CHANGE `" + name + "` `" + name + "` ";
                sqlSb.append(str);
            } else {
                String str = " ADD `" + name + "` ";
                sqlSb.append(str);
            }
            sqlSb.append(" " + ColumnTypeEnum.getOracleType(type) + " DEFAULT NULL COMMENT '" + comment + "' ");
            sqlList.add(sqlSb.toString());
        }

        String[] sqlArr = sqlList.toArray(new String[0]);
        for (String str : sqlArr) {
            System.out.println(str);
        }
        jdbcTemplate.batchUpdate(sqlArr);
        return R.success();
    }
}
